﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhouYu.DreamTour.Core;
using ZhouYu.DreamTour.DAL.IRepository;
using ZhouYu.DreamTour.Model;

namespace ZhouYu.DreamTour.DAL.Repository
{
    public class SpecialtyRepository: ISpecialtyRepository
    {
        #region 地方特产页面信息展示
        /// <summary>
        /// 地方特产页面信息展示
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public List<Goods> GetGoodsList()
        {
            //sql语句
            string sql = "SELECT * from Goods";
            sql += " limit 0,20";
            //调用查询集合方法
            List<Goods> goodsLists = DapperHelper<Goods>.QueryList(sql, null);
            return goodsLists;
        }
        #endregion


        #region 地方特产页面图片展示
        /// <summary>
        /// 地方特产页面图片展示
        /// </summary>
        /// <param name="userInfo"></param>
        /// <returns></returns>
        public List<Picture> GetPictureList()
        {
            //sql语句
            string sql = "SELECT * FROM Picture";
            //调用查询集合方法
            List<Picture> pictureLists = DapperHelper<Picture>.QueryList(sql, null);
            return pictureLists;
        }
        #endregion


        #region 根据Id获取地方特产信息
        /// <summary>
        /// 根据Id获取地方特产信息
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public Goods GetGoodsInfoById(int GoodsId)
        {
            //sql语句
            string sql = "select * from Goods WHERE GoodsId = @GoodsId";
            //创建一个地方特产对象
            Goods goods = new Goods();
            goods.GoodsId = GoodsId;
            //调用查询方法
            return DapperHelper<Goods>.QuerySingleOrDefault(sql, goods);
        }
        #endregion


        #region 根据商品Id查询商品发货地
        /// <summary>
        /// 根据商品Id查询商品发货地
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public Supplier CheckStartPlaceByGoodsId(int GoodsId)
        {
            //sql语句
            string sql = "SELECT SupplierName,SupplierAddress,SupplierTel,StartPlace FROM supplier AS a INNER JOIN goods AS b ON a.SupplierId = b.SupplierId where GoodsId=@GoodsId";
            //创建一个地方特产对象
            Goods goods = new Goods();
            goods.GoodsId = GoodsId;
            //调用查询方法
            return DapperHelper<Supplier>.QuerySingleOrDefault(sql,goods);
        }
        #endregion


        #region 根据用户Id查询商品配送地址
        /// <summary>
        /// 根据用户Id查询商品配送地址
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public DeliveryAddress CheckDeliAdressByUser_Id(int User_Id)
        {
            //sql语句
            string sql = "SELECT * FROM deliveryaddress WHERE User_Id=@User_Id";
            //创建一个配送地址对象
            DeliveryAddress deliveryAddress = new DeliveryAddress();
            deliveryAddress.User_Id= User_Id;
            //调用查询方法
            return DapperHelper<DeliveryAddress>.QueryFirstOrDefault(sql, deliveryAddress);
        }
        #endregion


        #region 根据订单类型和商品名称查询销售量
        /// <summary>
        /// 根据订单类型和商品名称查询销售量
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public int GetSalesCount(string GoodsName)
        {
            //sql语句
            string sql = "SELECT COUNT(1) from orderinfo WHERE Order_ProInfo=@Order_ProInfo AND Order_Type='特产'";
            //创建一个订单对象
            OrderInfo order = new OrderInfo();
            order.Order_ProInfo = GoodsName;
            //调用查询数量方法
            return Convert.ToInt32(DapperHelper<Goods>.ExecuteScalar(sql, order));
        }
        #endregion


        #region 根据商品Id获取地方特产详情信息
        /// <summary>
        /// 根据商品Id获取地方特产详情信息
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public GoodsDetails GetGoodsDetailsInfoById(int GoodsId)
        {
            //sql语句
            string sql = "SELECT * FROM GoodsDetails WHERE GoodsId=@GoodsId";
            //创建一个商品详情对象
            GoodsDetails goodsDetails = new GoodsDetails();
            goodsDetails.GoodsId = GoodsId;
            //调用查询方法
            return DapperHelper<GoodsDetails>.QuerySingleOrDefault(sql,goodsDetails);
        }
        #endregion


        #region 查询用户平均点评分数
        /// <summary>
        /// 查询用户平均点评分数
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public Usercomment CheckAvgScore(int GoodsId)
        {
            Usercomment usercomment = new Usercomment();
            usercomment.UserComment_PruId = GoodsId;
            //sql语句
            string sql = "SELECT UserComment_PruId,AVG(UserComment_Score) AS UserComment_Score  FROM UserComment WHERE UserComment_Type=0 AND UserComment_PruId=@UserComment_PruId;"; 
            //调用查询方法
            return DapperHelper<Usercomment>.QuerySingleOrDefault(sql, usercomment);
        }
        #endregion


        #region 查询特产评论数
        /// <summary>
        /// 查询特产评论数
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public int CheckUserCommentCount(int GoodsId)
        {
            //sql语句
            string sql = "SELECT Count(1) FROM UserComment WHERE UserComment_Type=0 and UserComment_PruId=@UserComment_PruId";
            //创建评论对象
            Usercomment usercomment = new Usercomment();
            usercomment.UserComment_PruId = GoodsId;
            //调用查询数量方法
            return Convert.ToInt32(DapperHelper<Usercomment>.ExecuteScalar(sql, usercomment));
        }
        #endregion


        #region 获取商品评论列表
        /// <summary>
        /// 获取商品评论列表
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public List<Usercomment> GetUsercommentsList(int GoodsId)
        {
            //sql语句
            string sql = "SELECT UserComment_Content  FROM UserComment WHERE UserComment_Type=0 and UserComment_PruId=@UserComment_PruId";
            //创建评论对象
            Usercomment usercomment = new Usercomment();
            usercomment.UserComment_PruId = GoodsId;
            //调用查询集合方法
            return DapperHelper<Usercomment>.QueryList(sql, usercomment);
        }
        #endregion


        #region 根据用户Id查询所有配送地址
        /// <summary>
        /// 根据用户Id查询所有配送地址
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public List<DeliveryAddress> CheckAllDeliAdressByUser_Id(int User_Id)
        {
            //sql语句
            string sql = "SELECT * FROM deliveryaddress WHERE User_Id=@User_Id";
            //创建一个配送地址对象
            DeliveryAddress deliveryAddress = new DeliveryAddress();
            deliveryAddress.User_Id = User_Id;
            //调用查询集合方法
            return DapperHelper<DeliveryAddress>.QueryList(sql, deliveryAddress);
        }
        #endregion


        #region 添加商品订单信息
        /// <summary>
        /// 添加商品订单信息
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public int AddGoodsOrderInfo(OrderInfo orderInfo)
        {
            //sql语句
            string sql = @"INSERT INTO orderinfo(Order_No,User_Id,Orde_PictureUrl,Order_ProInfo,Order_Money,Order_ReserveTime,ModifiedTime,Order_Content,Order_Name,Order_Type,Order_Status,Order_OutingTime)
                           VALUES(@Order_No,@User_Id,@Orde_PictureUrl,@Order_ProInfo, @Order_Money,@Order_ReserveTime, @ModifiedTime, @Order_Content,'新世界杂货店','特产',0,@Order_OutingTime)";         
            //给预定时间和修改时间赋值
            orderInfo.Order_ReserveTime = DateTime.Now;
            orderInfo.ModifiedTime = DateTime.Now;
            orderInfo.Order_OutingTime = DateTime.Now.AddDays(5);
            //调用添加方法
            return DapperHelper<OrderInfo>.Execute(sql,orderInfo);
        }
        #endregion


        #region 根据用户Id查询最新一条订单
        /// <summary>
        /// 根据用户Id查询最新一条订单
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public OrderInfo CheckNewOrder(int User_Id)
        {
            //sql语句
            string sql = "select * from OrderInfo where User_Id=@User_Id ORDER BY Order_Id DESC limit 1";
            //创建一个订单对象
            OrderInfo orderInfo = new OrderInfo();
            orderInfo.User_Id = User_Id;
            //调用查询方法
            return DapperHelper<OrderInfo>.QuerySingleOrDefault(sql,orderInfo);
        }
        #endregion


        #region 添加订单时同时将收货人信息添加到订单收货中
        /// <summary>
        /// 添加订单时同时将收货人信息添加到订单收货中
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public int AddOrderNowToOrderOver(OrderOver orderOver)
        {
            //sql语句
            string sql = @"INSERT INTO OrderOver(Order_Id,OrderOver_Name,OrderOver_Tel,OrderOver_Address,OrderOver_CreateTime,OrderOver_ExpireTime)
                           VALUES(@Order_Id,@OrderOver_Name,@OrderOver_Tel,@OrderOver_Address,@OrderOver_CreateTime,@OrderOver_ExpireTime)";
            //给创建时间和修改时间赋值
            orderOver.OrderOver_CreateTime = DateTime.Now;
            orderOver.OrderOver_ExpireTime = DateTime.Now;           
            //调用添加方法
            return DapperHelper<OrderOver>.Execute(sql, orderOver);
        }
        #endregion


        #region 根据订单编号查询商品信息
        /// <summary>
        /// 根据订单Id查询商品信息
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public OrderInfo CheckGoodsInfoByOrder_No(string Order_No)
        {
            //sql语句
            string sql = "SELECT * FROM OrderInfo WHERE Order_No=@Order_No";
            //创建一个订单对象
            OrderInfo orderInfo = new OrderInfo();
            orderInfo.Order_No = Order_No;
            //调用查询方法
            return DapperHelper<OrderInfo>.QuerySingleOrDefault(sql,orderInfo);
        }
        #endregion


        #region 根据订单Id查询收货人信息
        /// <summary>
        /// 根据订单Id查询收货人信息
        /// </summary>
        /// <param name="TourId"></param>
        /// <returns></returns>
        public OrderOver CheckOrderOverInfoByOrder_Id(int Order_Id)
        {
            //sql语句
            string sql = "SELECT * FROM OrderOver WHERE Order_Id=@Order_Id";
            //创建一个订单对象
            OrderOver orderOver = new OrderOver();
            orderOver.Order_Id = Order_Id;
            //调用查询方法
            return DapperHelper<OrderOver>.QuerySingleOrDefault(sql,orderOver);
        }
        #endregion


        #region 根据订单编号修改订单状态为待评价
        /// <summary>
        /// 根据订单编号修改订单状态为待评价
        /// </summary>
        /// <returns></returns>
        public int UpdateOrderStatusByOrder_No(string Order_No)
        {
            //sql语句
            string sql = "UPDATE orderinfo SET Order_Status=1 WHERE Order_No=@Order_No";
            //创建一个订单对象
            OrderInfo orderInfo = new OrderInfo();
            orderInfo.Order_No = Order_No;
            //调用修改方法
            int result = DapperHelper<OrderInfo>.Execute(sql, orderInfo);
            return result;
        }
        #endregion


    }
}
